package com.dushibao.dao;

import com.dushibao.entity.Menu;

import com.dushibao.util.DBUtils;
import com.dushibao.util.Page;
import com.dushibao.vo.MenuVO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class MenuDao {
    public List<Menu> list(){
        try{

            StringBuilder sql = new StringBuilder();
            sql.append(" SELECT");
            sql.append(" 	p.id AS pid,");
            sql.append(" 	p.addTime AS paddTime,");
            sql.append(" 	p.NAME AS pname,");
            sql.append(" 	p.url AS purl,");
            sql.append(" 	p.icon AS picon,");
            sql.append(" 	p.parent_id AS pparent_id,");
            sql.append(" 	c.id AS cid,");
            sql.append(" 	c.addTime AS caddTime,");
            sql.append(" 	c.NAME AS cname,");
            sql.append(" 	c.url AS curl,");
            sql.append(" 	c.icon AS cicon,");
            sql.append(" 	c.parent_id AS cparent_id");
            sql.append(" FROM");
            sql.append(" 	dushibao_menu p");
            sql.append(" LEFT JOIN dushibao_menu c ON p.id = c.parent_id");
            sql.append(" WHERE");
            sql.append(" 	p.parent_id IS NULL");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());

            ResultSet rs = stmt.executeQuery();

            List<MenuVO> menuVOS = new ArrayList<>();
            while(rs.next()){
                MenuVO menuVO = new MenuVO();

                menuVO.setPid(rs.getLong("pid"));
                menuVO.setPaddTime(rs.getTimestamp("paddTime").toLocalDateTime());
                menuVO.setPname(rs.getString("pname"));
                menuVO.setPurl(rs.getString("purl"));
                menuVO.setPicon(rs.getString("picon"));
                menuVO.setPparentId(rs.getLong("pparent_id"));

                if(rs.getTimestamp("caddTime") != null){
                    menuVO.setCid(rs.getLong("cid"));
                    menuVO.setCaddTime(rs.getTimestamp("caddTime").toLocalDateTime());
                    menuVO.setCname(rs.getString("cname"));
                    menuVO.setCurl(rs.getString("curl"));
                    menuVO.setCicon(rs.getString("cicon"));
                    menuVO.setCparentId(rs.getLong("cparent_id"));
                }
                menuVOS.add(menuVO);
            }

            Map<Long,List<MenuVO>> menuMaps = menuVOS.stream().collect(Collectors.groupingBy(t->t.getPid()));

            List<Menu> menus = new ArrayList<>();

            for (Long key : menuMaps.keySet()) {
                List<MenuVO> pcMenus = menuMaps.get(key);
                Menu parent = new Menu();
                parent.setId(pcMenus.get(0).getPid());
                parent.setAddTime(pcMenus.get(0).getPaddTime());
                parent.setName(pcMenus.get(0).getPname());
                parent.setUrl(pcMenus.get(0).getPurl());
                parent.setIcon(pcMenus.get(0).getPicon());
                parent.setParentId(pcMenus.get(0).getPparentId());

                List<Menu> childs = new ArrayList<>();
                for (MenuVO pcMenu : pcMenus) {
                    if(pcMenu.getCid() == null){
                        continue;
                    }
                    Menu child = new Menu();
                    child.setId(pcMenu.getCid());
                    child.setAddTime(pcMenu.getCaddTime());
                    child.setName(pcMenu.getCname());
                    child.setUrl(pcMenu.getCurl());
                    child.setIcon(pcMenu.getCicon());
                    child.setParentId(pcMenu.getCparentId());
                    childs.add(child);
                }
                if(childs.size()>0) {
                    parent.setChilds(childs);
                }
                menus.add(parent);
            }

            menus.sort( (m1,m2)-> m1.getId().compareTo(m2.getId()));

            for (Menu menu : menus) {
                if(menu.getChilds().size()>0) {
                    menu.getChilds().sort((m1, m2) -> m1.getId().compareTo(m2.getId()));
                }
            }

            return menus;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    public Page page(Page page, String menuName, String menuUrl) {

        try{
            StringBuilder querySql = new StringBuilder("select c.id,c.addTime,c.name,c.url,c.icon,c.parent_id,p.name as parentName from dushibao_menu c left join dushibao_menu p on c.parent_id = p.id where 1=1 ");
            if(menuName != null && menuName.length()>0){
                querySql.append(" and c.name  like '%"+menuName+"%'");
            }
            if(menuUrl != null && menuUrl.length()>0){
                querySql.append(" and c.url like '%"+menuUrl+"%' ");
            }
            querySql.append(" limit ?,? ");

            Connection conn = DBUtils.getConnection();

            PreparedStatement stmt = conn.prepareStatement(querySql.toString());
            stmt.setInt(1,page.getStart());
            stmt.setInt(2,page.getPageSize());

            ResultSet rs = stmt.executeQuery();

            List<Object> menus = new ArrayList<>();
            while(rs.next()){
                Menu menu = new Menu();
                menu.setId(rs.getLong("id"));
                menu.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                menu.setName(rs.getString("name"));
                menu.setUrl(rs.getString("url"));
                menu.setIcon(rs.getString("icon"));
                menu.setParentId(rs.getLong("parent_id"));
                menu.setParentName(rs.getString("parentName"));
                menus.add(menu);
            }

            DBUtils.close(rs,stmt,conn);

            StringBuilder countSql = new StringBuilder("select count(*) as total from dushibao_menu where 1=1 ");
            if(menuName != null && menuName.length()>0){
                countSql.append(" and name  like '%"+menuName+"%'");
            }
            if(menuUrl != null && menuUrl.length()>0){
                countSql.append(" and url like '%"+menuUrl+"%' ");
            }

            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement(countSql.toString());

            rs = stmt.executeQuery();

            int total = 0;
            if(rs.next()){
                total = rs.getInt("total");
            }

            Page iPage = new Page();
            iPage.setCurrent(page.getCurrent());
            iPage.setPageSize(page.getPageSize());
            iPage.setData(menus);
            iPage.setPages(total % iPage.getPageSize()==0?total/ iPage.getPageSize():total/ iPage.getPageSize()+1);
            iPage.setTotal(total);

            return iPage;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public Menu getById(long id) {
        try{
            String sql = "select id,addTime,name,url,icon,parent_id from dushibao_menu where id = ?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setLong(1,id);
            ResultSet rs = stmt.executeQuery();
            Menu menu = new Menu();

            if(rs.next()){
                menu.setId(rs.getLong("id"));
                menu.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                menu.setName(rs.getString("name"));
                menu.setUrl(rs.getString("url"));
                menu.setIcon(rs.getString("icon"));
                menu.setParentId(rs.getLong("parent_id"));
            }
            return menu;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    public Integer update(Menu menu) {
        try{
            String sql = "update dushibao_menu set name=?,url=?,icon=?,parent_id=? where id=?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,menu.getName());
            stmt.setString(2,menu.getUrl());
            stmt.setString(3,menu.getIcon());
            if(menu.getParentId()==null){
                stmt.setNull(4, Types.BIGINT);
            }else {
                stmt.setLong(4, menu.getParentId());
            }
            stmt.setLong(5,menu.getId());

            stmt.executeUpdate();

            return 1000;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public Integer save(Menu menu) {
        try{
            String sql = "insert into dushibao_menu(name,url,parent_id) values(?,?,?)";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1,menu.getName());
            stmt.setString(2,menu.getUrl());
            if(menu.getParentId()==null){
                stmt.setNull(3, Types.BIGINT);
            }else{
                stmt.setLong(3,menu.getParentId());
            }
            stmt.executeUpdate();
            DBUtils.close(stmt,conn);
            return 1000;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

    public Integer deleteById(List<Long> ids) {
        try{
            StringBuilder sql = new StringBuilder(" delete from dushibao_menu where id in ( ");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(ids.get(i));

                if(i<ids.size()-1){
                    sql.append(",");
                }
            }
            sql.append(")");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            return 1000;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}
